﻿using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Shuba.Model;
using System;


namespace Shuba.Data
{
    public class RegionalClusterMap
    {
        public RegionalClusterMap()
        {
        }

        public List<RegionalClusterMapInfo> GetList(string value)
        {
            List<RegionalClusterMapInfo> list = new List<RegionalClusterMapInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"
                                SELECT 
            	                    A.REGIONAL_ID, C.REGIONAL_NAME, A.CLUSTER_ID, 
                                    B.CLUSTER_NAME, A.ENTRY_BY, A.ENTRY_DATE, 
                                    A.UPDATE_BY, A.UPDATE_DATE
                                FROM 
            	                    TBL_MS_CLUSTER B INNER JOIN TBL_MS_REGION_CLUSTER A 
            	                    ON B.CLUSTER_ID = A.CLUSTER_ID INNER JOIN TBL_MS_REGIONAL C 
            	                    ON A.REGIONAL_ID = C.REGIONAL_ID
                                WHERE 
                                    C.REGIONAL_NAME LIKE '%' + @REGIONAL_NAME + '%' 
                                ORDER BY C.REGIONAL_NAME
                               ");
            db.AddInParameter(cmd, "@REGIONAL_NAME", DbType.String, value);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new RegionalClusterMapInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDateTime(5), dr.GetString(6), dr.GetDateTime(7)));
                }
                dr.Close();
            }
            return list;
        }

        public RegionalClusterMapInfo Get(string RegionalId, string ClusterId)
        {
            RegionalClusterMapInfo ri = new RegionalClusterMapInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_MS_REGION_CLUSTER WHERE REGIONAL_ID LIKE '' + @REGIONAL_ID + '' AND CLUSTER_ID LIKE '' + @CLUSTER_ID + ''");
            db.AddInParameter(cmd, "@REGIONAL_ID", DbType.String, RegionalId);
            db.AddInParameter(cmd, "@CLUSTER_ID", DbType.String, ClusterId);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ri = new RegionalClusterMapInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetString(4), dr.GetDateTime(5));
                }
                dr.Close();
            }

            return ri;
        }

        public RegionalClusterMapInfo GetApprovalByID(string RegionalID, string ClusterID)
        {
            RegionalClusterMapInfo ri = new RegionalClusterMapInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT 
            	                        A.REGIONAL_ID, ISNULL(C.REGIONAL_NAME,'') AS REGIONAL_NAME, A.CLUSTER_ID, 
                                        ISNULL(B.CLUSTER_NAME,'') AS CLUSTER_NAME, A.ENTRY_BY, A.ENTRY_DATE, 
                                        A.UPDATE_BY, A.UPDATE_DATE, A.ACT_TYPE, A.REGIONAL_ID_ORIG, A.CLUSTER_ID_ORIG
                                    FROM 
            	                        TBL_MS_CLUSTER B RIGHT OUTER JOIN TBL_MS_REGION_CLUSTER_TMP A 
            	                        ON B.CLUSTER_ID = A.CLUSTER_ID LEFT OUTER JOIN TBL_MS_REGIONAL C 
            	                        ON A.REGIONAL_ID = C.REGIONAL_ID
                                    WHERE A.REGIONAL_ID LIKE '' + @REGIONAL_ID + '' AND A.CLUSTER_ID LIKE '' + @CLUSTER_ID + ''");
            db.AddInParameter(cmd, "@REGIONAL_ID", DbType.String, RegionalID);
            db.AddInParameter(cmd, "@CLUSTER_ID", DbType.String, ClusterID);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ri = new RegionalClusterMapInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDateTime(5), dr.GetString(6), dr.GetDateTime(7), dr.GetString(8), dr.GetString(9), dr.GetString(10));
                }
                dr.Close();
            }
            return ri;
        }

        public List<RegionalClusterMapInfo> GetListApproval(string value, string UpdateBy)
        {
            List<RegionalClusterMapInfo> list = new List<RegionalClusterMapInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"
                                SELECT * FROM
                                (
                                    SELECT 
            	                        A.REGIONAL_ID, ISNULL(C.REGIONAL_NAME,'') AS REGIONAL_NAME, A.CLUSTER_ID, 
                                        ISNULL(B.CLUSTER_NAME,'') AS CLUSTER_NAME, A.ENTRY_BY, A.ENTRY_DATE, 
                                        A.UPDATE_BY, A.UPDATE_DATE, A.ACT_TYPE, A.REGIONAL_ID_ORIG, A.CLUSTER_ID_ORIG
                                    FROM 
            	                        TBL_MS_CLUSTER B RIGHT OUTER JOIN TBL_MS_REGION_CLUSTER_TMP A 
            	                        ON B.CLUSTER_ID = A.CLUSTER_ID LEFT OUTER JOIN TBL_MS_REGIONAL C 
            	                        ON A.REGIONAL_ID = C.REGIONAL_ID
                                ) X
                                WHERE 
                                    REGIONAL_NAME LIKE '%' + @REGIONAL_NAME + '%' 
                                    AND (RTRIM(UPDATE_BY) <> '' + @UPDATE_BY + '') 
                                ORDER BY REGIONAL_NAME
                                ");
            db.AddInParameter(cmd, "@REGIONAL_NAME", DbType.String, value);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, UpdateBy);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new RegionalClusterMapInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDateTime(5), dr.GetString(6), dr.GetDateTime(7), dr.GetString(8), dr.GetString(9), dr.GetString(10)));
                }
                dr.Close();
            }
            return list;
        }

        public void Save(RegionalClusterMapInfo RegionalCluster)
        {
            Database db = DBConnection.CreateDatabase();

            string sql = @"
                                UPDATE [TBL_MS_REGION_CLUSTER_TMP]
                                   SET [REGIONAL_ID] = @REGIONAL_ID
                                      ,[CLUSTER_ID] = @CLUSTER_ID
                                      ,[ENTRY_BY] = @ENTRY_BY
                                      ,[ENTRY_DATE] = @ENTRY_DATE
                                      ,[UPDATE_BY] = @UPDATE_BY
                                      ,[UPDATE_DATE] = @UPDATE_DATE
                                      ,[REGIONAL_ID_ORIG] = @REGIONAL_ID_ORIG
                                      ,[CLUSTER_ID_ORIG] = @CLUSTER_ID_ORIG
                                WHERE REGIONAL_ID LIKE '' + @REGIONAL_ID + '' AND CLUSTER_ID LIKE '' + @CLUSTER_ID + '' ;
                                IF @@ROWCOUNT = 0
                                INSERT INTO [TBL_MS_REGION_CLUSTER_TMP] ([REGIONAL_ID],[CLUSTER_ID],[ENTRY_BY],[ENTRY_DATE],[UPDATE_BY],[UPDATE_DATE],[ACT_TYPE],[REGIONAL_ID_ORIG],[CLUSTER_ID_ORIG]) " +
                              " VALUES (@REGIONAL_ID, @CLUSTER_ID, @ENTRY_BY, @ENTRY_DATE, @UPDATE_BY, @UPDATE_DATE, @ACT_TYPE, @REGIONAL_ID_ORIG, @CLUSTER_ID_ORIG)";
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@REGIONAL_ID", DbType.String, RegionalCluster.RegionalID);
            db.AddInParameter(cmd, "@CLUSTER_ID", DbType.String, RegionalCluster.ClusterID);
            db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, RegionalCluster.EntryBy);
            db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, RegionalCluster.EntryDate);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, RegionalCluster.UpdateBy);
            db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, RegionalCluster.UpdateDate);
            db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, RegionalCluster.ActType);
            db.AddInParameter(cmd, "@REGIONAL_ID_ORIG", DbType.String, RegionalCluster.RegionalIDOrig ?? RegionalCluster.RegionalID);
            db.AddInParameter(cmd, "@CLUSTER_ID_ORIG", DbType.String, RegionalCluster.ClusterIDOrig ?? RegionalCluster.ClusterID);

            db.ExecuteNonQuery(cmd);
        }

        public void Approve(string RegionalId, string ClusterId, string ActType)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetStoredProcCommand("REGIONAL_CLUSTER_APPROVAL");

            db.AddInParameter(cmd, "@REGIONAL_ID", DbType.String, RegionalId);
            db.AddInParameter(cmd, "@CLUSTER_ID", DbType.String, ClusterId);
            db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, ActType);

            db.ExecuteNonQuery(cmd);
        }

        public void Reject(string RegionalId, string ClusterId)
        {
            Database db = DBConnection.CreateDatabase();

            string sql = String.Format("DELETE FROM TBL_MS_REGION_CLUSTER_TMP WHERE REGIONAL_ID LIKE '{0}' AND CLUSTER_ID LIKE '{1}'", RegionalId, ClusterId);
            DbCommand cmd = db.GetSqlStringCommand(sql);

            db.ExecuteNonQuery(cmd);
        }
    }
}
